import java.io.*;
import java.net.ServerSocket;
import java.net.Socket;
import java.sql.Connection;
import java.util.Scanner;

import org.json.JSONException;
import org.json.JSONObject;
import org.json.JSONArray;
import java.util.*;
import java.util.Date;
import java.sql.*;

public class Main {



    private ArrayList<JSONObject> cache=new ArrayList<JSONObject>();
    private ArrayList<String> cacheDeviceID=new ArrayList<String>();






    public void equipMsg() {


        Connection con = Connect.getConn();

        try {
            ServerSocket serverSocket = new ServerSocket(28881);

            while (true) {
                JSONObject returnMsg = new JSONObject();
                Socket socket = serverSocket.accept();

                DataInputStream inputstream=new DataInputStream(socket.getInputStream());

                int c;
                String str = "";
                do {
                    c = inputstream.read();
                    str += (char) c;
                } while (inputstream.available() > 0);
                System.out.println(str);

                try {
                    JSONObject json = new JSONObject(str);
                    JSONArray uploads = json.getJSONArray("uploads");

                    int mode=0;


                    JSONArray orders=new JSONArray();

                    for (Object equip : uploads) {

                        JSONObject message=(JSONObject)equip;


                        String id = message.getString("id");
                        String route = message.getString("route");
                        JSONObject data;

                        String sql = "";
                        Statement SQLStatement = con.createStatement();
                        ResultSet res;

                        int err = 0;

                        switch (route) {

                            case "fanUpload":

                                data = message.getJSONObject("data");

                                int stageFan=data.getInt("stage");
                                String humi=""+data.getFloat("humi");
                                String temp=""+data.getFloat("temp");

                                sql = "UPDATE Fan SET isOpen = "+(stageFan==0?"0":"1")+
                                        ", Gear = "+stageFan+", ptemp = '"+temp+
                                        "' WHERE Device_idDevice = '"+id+"'";
                                SQLStatement.execute(sql);

                                sql="UPDATE Temperature SET tempNum = '"+temp+
                                        "' WHERE Device_idDevice = '"+id+"'";
                                SQLStatement.execute(sql);

                                sql="UPDATE Humidity SET hygro = '"+humi+
                                        "' WHERE Device_idDevice = '"+id+"'";
                                SQLStatement.execute(sql);
                                mode=1;


                                break;

                            case "doorUpload":

                                data = message.getJSONObject("data");

                                int stateDoor=data.getInt("state");


                                sql = "UPDATE Doors SET isOpen = "+stateDoor+
                                        " WHERE Device_idDevice = '"+id+"'";
                                SQLStatement.execute(sql);
                                if(stateDoor==1){

                                    sql="SELECT opentimes FROM Doors WHERE Device_idDevice = '"+id+"'";
                                    res=SQLStatement.executeQuery(sql);

                                    res.next();
                                    int current=Integer.parseInt(res.getString("opentimes"))+1;

                                    sql="UPDATE Doors SET opentimes = '"+current+
                                            "' WHERE Device_idDevice = '"+id+"'";

                                    SQLStatement.execute(sql);
                                    mode=1;


                                }

                                break;

                            case "windowUpload":

                                data = message.getJSONObject("data");

                                int stateWindow=data.getInt("state");


                                sql = "UPDATE Windows SET isOpen = "+stateWindow+
                                        " WHERE Device_idDevice = '"+id+"'";
                                SQLStatement.execute(sql);
                                if(stateWindow==1){

                                    sql="SELECT opentimes FROM Windows WHERE Device_idDevice = '"+id+"'";
                                    res=SQLStatement.executeQuery(sql);

                                    res.next();
                                    int current=Integer.parseInt(res.getString("opentimes"))+1;

                                    sql="UPDATE Windows SET opentimes = '"+current+
                                            "' WHERE Device_idDevice = '"+id+"'";

                                    SQLStatement.execute(sql);
                                    mode=1;


                                }

                                break;

                            case "lightUpload":

                                data = message.getJSONObject("data");


                                int level=data.getInt("level");
                                int color=data.getInt("color");


                                sql = "UPDATE Lamps SET isOn = "+(level==0?"0":"1")+", gear = '"+level+"', color = '"+color+
                                        "' WHERE Device_idDevice = '"+id+"'";
                                SQLStatement.execute(sql);
                                mode=1;


                                break;


                            case "secUpload":

                                data = message.getJSONObject("data");


                                int stateSec=data.getInt("state");
                                int secmode=data.getInt("secmode");


                                sql = "UPDATE Alarm SET alarmTriggered = '"+stateSec+"', alarmmode = '"+secmode+
                                        "' WHERE Device_idDevice = '"+id+"'";
                                SQLStatement.execute(sql);
                                mode=1;


                                break;


                            case "fireUpload":

                                data = message.getJSONObject("data");


                                int stateFire=data.getInt("state");



                                sql = "UPDATE firealarm SET istriggered = "+stateFire+
                                        " WHERE Device_idDevice = '"+id+"'";
                                SQLStatement.execute(sql);
                                mode=1;


                                break;

                            case "coverUpload":

                                data = message.getJSONObject("data");


                                int stageCover=data.getInt("stage");
                                int illu=data.getInt("illu");


                                sql = "UPDATE AutoSunVisor SET isOpen = "+(stageCover==0?"0":"1")+", gear = '"+stageCover+
                                        "', pbrightness = '"+illu+
                                        "' WHERE Device_idDevice = '"+id+"'";
                                SQLStatement.execute(sql);
                                mode=1;


                                break;

                            case "setupDownload":



                                mode=-1;
                                int selectType;
                                JSONObject curOrder=new JSONObject();

                                String[] deviceTypeArray={"Fan","Doors","Lamps","Alarm","AutoSunVisor"};


                                for(selectType=0;selectType<deviceTypeArray.length;selectType++){
                                    sql="SELECT Device_idDevice FROM "+deviceTypeArray[selectType]+
                                    " WHERE Device_idDevice = '"+id+"'";
                                    res= SQLStatement.executeQuery(sql);
                                    if(res.next()) break;
                                }
                                switch (selectType){


                                    case 0:

                                        sql="SELECT Gear, stemp FROM Fan WHERE Device_idDevice = '"+id+"'";
                                        res=SQLStatement.executeQuery(sql);
                                        res.next();

                                        curOrder.put("optName","changeFanState");
                                        curOrder.put("newState",res.getInt("Gear"));
                                        curOrder.put("newOpenTemp",Float.parseFloat(res.getString("stemp")));
                                        orders.put(curOrder);

                                        break;

                                    case 1:

                                        JSONObject psw=new JSONObject();

                                        sql="SELECT Password, isOpen FROM Doors WHERE Device_idDevice = '"+id+"'";
                                        res=SQLStatement.executeQuery(sql);
                                        res.next();
                                        psw.put("optName","changePW");
                                        psw.put("newPassword",Long.parseLong(res.getString("Password")));
                                        orders.put(psw);

                                        curOrder.put("optName","changeDoorState");
                                        curOrder.put("newState",res.getInt("isOpen"));
                                        orders.put(curOrder);

                                        break;

                                    case 2:

                                        sql="SELECT gear, color, Red, Green, Blue FROM Lamps WHERE Device_idDevice = '"+id+"'";
                                        res=SQLStatement.executeQuery(sql);
                                        res.next();
                                        curOrder.put("optName","changeLight");
                                        curOrder.put("level",Integer.parseInt(res.getString("gear")));
                                        curOrder.put("color",Integer.parseInt(res.getString("color")));
                                        if(res.getInt("color")==0){
                                            curOrder.put("R",res.getInt("Red"));
                                            curOrder.put("G",res.getInt("Green"));
                                            curOrder.put("B",res.getInt("Blue"));
                                        }
                                        else{
                                            curOrder.put("R","");
                                            curOrder.put("G","");
                                            curOrder.put("B","");
                                        }
                                        orders.put(curOrder);

                                        break;

                                    case 3:

                                        sql="SELECT alarmmode FROM Alarm WHERE Device_idDevice = '"+id+"'";
                                        res=SQLStatement.executeQuery(sql);
                                        res.next();
                                        curOrder.put("optName","changeSecMode");
                                        curOrder.put("secmode",Integer.parseInt(res.getString("alarmmode")));
                                        orders.put(curOrder);

                                        break;

                                    case 4:

                                        sql="SELECT gear, sbrightness FROM AutoSunVisor WHERE Device_idDevice = '"+id+"'";
                                        res=SQLStatement.executeQuery(sql);
                                        res.next();
                                        curOrder.put("optName","changeCoverState");
                                        curOrder.put("newState",Integer.parseInt(res.getString("gear")));
                                        curOrder.put("newOpenILLU",Float.parseFloat(res.getString("sbrightness")));
                                        orders.put(curOrder);

                                }

                                break;

                            case "ctrlDownload":


                                mode=-1;

                                for(int i=0;i<cacheDeviceID.size();i++){
                                    if(id.equals(cacheDeviceID.get(i))){
                                        orders.put(cache.get(i));
                                    }
                                }
                                while(cacheDeviceID.contains(id)){
                                    cache.remove(cacheDeviceID.lastIndexOf(id));
                                    cacheDeviceID.remove(cacheDeviceID.lastIndexOf(id));
                                }

                                break;

                        }

                    }

                    returnMsg.put("success", 1);
                    returnMsg.put("errmsg", "");
                    if(mode==-1){
                        returnMsg.put("opt",orders);
                    }

                    System.out.println(returnMsg.toString());


                    BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(socket.getOutputStream()));
                    bw.write(returnMsg.toString());

                    bw.flush();





                } catch (IOException | SQLException |JSONException e) {
                    e.printStackTrace();
                    returnMsg.put("success", 0);
                    returnMsg.put("errmsg", e.toString());

                    BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(socket.getOutputStream()));
                    bw.write(returnMsg.toString());

                    bw.flush();


                }



                socket.close();



            }

        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    public void usrMsg() {


        Date date=new Date();


        Connection con = Connect.getConn();

        BufferedWriter bw;


        try {
            ServerSocket serverSocket = new ServerSocket(28880);
            while (true) {
                JSONObject returnMsg = new JSONObject();
                Socket socket = serverSocket.accept();

                DataInputStream inputstream=new DataInputStream(socket.getInputStream());

                int c;
                String str = "";
                do {
                    c = inputstream.read();
                    str += (char) c;
                } while (inputstream.available() > 0);
                System.out.println(str);
                try {
                    JSONObject message = new JSONObject(str);

                    String route = message.getString("route");
                    JSONObject data;

                    String sql = "";
                    Statement SQLStatement = con.createStatement();
                    ResultSet res;
                    ResultSet resultset;

                    data = message.getJSONObject("data");
                    String username = "";
                    String UID="";
                    String familyID="";
                    String deviceID="";
                    String changeDeviceID="";


                    switch(route){


                        case "webLogin":


                            username=data.getString("username");

                            int action=data.getInt("action");

                            String password = data.getString("password");


                            switch(action){

                                case 1:

                                    sql="SELECT Password FROM Account WHERE aName = '"+username+"'";
                                    res=SQLStatement.executeQuery(sql);
                                    if(res.next()){
                                        if(res.getString("password").equals(password)){
                                            returnMsg.put("success", 1);
                                            returnMsg.put("errmsg", "");

                                            bw = new BufferedWriter(new OutputStreamWriter(socket.getOutputStream()));
                                            bw.write(returnMsg.toString());

                                            bw.flush();
                                        }
                                        else{
                                            returnMsg.put("success", 0);
                                            returnMsg.put("errmsg", "Wrong password");

                                            bw = new BufferedWriter(new OutputStreamWriter(socket.getOutputStream()));
                                            bw.write(returnMsg.toString());

                                            bw.flush();

                                        }
                                    }
                                    else{
                                        returnMsg.put("success", 0);
                                        returnMsg.put("errmsg", "Invalid username");

                                        bw = new BufferedWriter(new OutputStreamWriter(socket.getOutputStream()));
                                        bw.write(returnMsg.toString());

                                        bw.flush();

                                    }

                                    break;

                                case 2:

                                    sql="SELECT aName FROM Account";
                                    res= SQLStatement.executeQuery(sql);
                                    int valid=1;
                                    while(res.next()){
                                        if(res.getString("aName").equals(username)) {
                                            returnMsg.put("success", 0);
                                            returnMsg.put("errmsg", "Duplicate username");

                                            bw = new BufferedWriter(new OutputStreamWriter(socket.getOutputStream()));
                                            bw.write(returnMsg.toString());

                                            bw.flush();
                                            valid=0;
                                            break;

                                        }
                                    }
                                    if(valid==1){
                                        do {
                                            String testUID = "u" + (int) (Math.random() * 100000000);
                                            sql = "SELECT UID FROM Account";
                                            res = SQLStatement.executeQuery(sql);
                                            valid=1;
                                            while(res.next()){
                                                if(res.getString("UID").equals(testUID)){
                                                    valid=0;
                                                    break;
                                                }
                                            }
                                            if(valid==1){
                                                UID=testUID;
                                            }
                                        }
                                        while(valid==0);
                                        sql="INSERT INTO Account values('"+UID+"', '"+username+"', '"+password+"', 1)";
                                        SQLStatement.execute(sql);
                                        sql="INSERT INTO User values('','','',1,'"+password+"','"+UID+"','0',0)";
                                        SQLStatement.execute(sql);

                                        returnMsg.put("success", 1);
                                        returnMsg.put("errmsg", "");

                                        bw = new BufferedWriter(new OutputStreamWriter(socket.getOutputStream()));
                                        bw.write(returnMsg.toString());

                                        bw.flush();



                                    }

                                    break;


                            }

                            break;

                        case "changepsw":

                            username=data.getString("username");

                            String oldPassword = data.getString("oldpassword");
                            String newPassword = data.getString("newpassword");

                            sql="SELECT Password FROM Account WHERE aName = '"+username+"'";
                            res=SQLStatement.executeQuery(sql);
                            if(res.next()){
                                if(res.getString("password").equals(oldPassword)){

                                    sql="UPDATE Account SET Password = '"+newPassword+"' WHERE aName = '"+username+"'";
                                    SQLStatement.execute(sql);
                                    sql="SELECT * FROM Account WHERE aName = '"+username+"'";
                                    res= SQLStatement.executeQuery(sql);
                                    res.next();
                                    UID=res.getString("UID");

                                    sql="UPDATE User SET Password ='"+newPassword+"' WHERE uUID='"+UID+"'";
                                    SQLStatement.execute(sql);


                                    returnMsg.put("success", 1);
                                    returnMsg.put("errmsg", "");

                                    bw = new BufferedWriter(new OutputStreamWriter(socket.getOutputStream()));
                                    bw.write(returnMsg.toString());

                                    bw.flush();
                                }
                                else{
                                    returnMsg.put("success", 0);
                                    returnMsg.put("errmsg", "Wrong password");

                                    bw = new BufferedWriter(new OutputStreamWriter(socket.getOutputStream()));
                                    bw.write(returnMsg.toString());

                                    bw.flush();

                                }
                            }
                            else{
                                returnMsg.put("success", 0);
                                returnMsg.put("errmsg", "Invalid username");

                                bw = new BufferedWriter(new OutputStreamWriter(socket.getOutputStream()));
                                bw.write(returnMsg.toString());

                                bw.flush();

                            }

                            break;


                        case "showdeviceinfo":

                            username=data.getString("username");

                            sql="SELECT * FROM Account WHERE aName = '"+username+"'";
                            res=SQLStatement.executeQuery(sql);
                            res.next();
                            UID=res.getString("UID");
                            sql="SELECT * FROM User WHERE uUID = '"+UID+"'";
                            res=SQLStatement.executeQuery(sql);
                            res.next();
                            familyID=res.getString("Family_familyID");

                            sql="SELECT * FROM Device WHERE Family_familyID = '"+familyID+"'";
                            resultset=SQLStatement.executeQuery(sql);
                            int count=0;
                            while(resultset.next()){
                                count++;
                            }
                            sql="SELECT * FROM Device WHERE Family_familyID = '"+familyID+"'";
                            resultset=SQLStatement.executeQuery(sql);
                            String[] deviceInfos=new String[count];
                            for(int i=0;i<count;i++){
                                resultset.next();
                                deviceInfos[i]=resultset.getString("idDevice");
                            }

                            JSONArray deviceDatas=new JSONArray();
                            for(int i=0;i<count;i++){
                                JSONObject deviceInfo=new JSONObject();
                                deviceID=deviceInfos[i];

                                String[] deviceTypeArray={"Fan","Doors","Windows","Lamps","Alarm","AutoSunVisor","firealarm"};

                                int selectType;

                                for(selectType=0;selectType<deviceTypeArray.length;selectType++){
                                    sql="SELECT Device_idDevice FROM "+deviceTypeArray[selectType]+
                                            " WHERE Device_idDevice = '"+deviceID+"'";
                                    res= SQLStatement.executeQuery(sql);
                                    if(res.next()) break;
                                }
                                switch (selectType){


                                    case 0:

                                        sql="SELECT * FROM Fan WHERE Device_idDevice = '"+deviceID+"'";
                                        res=SQLStatement.executeQuery(sql);
                                        res.next();

                                        deviceInfo.put("Type","Fan");
                                        deviceInfo.put("deviceID",deviceID);
                                        deviceInfo.put("State",res.getInt("Gear"));
                                        deviceInfo.put("Temp",Float.parseFloat(res.getString("ptemp")));
                                        deviceInfo.put("OpenTemp",Float.parseFloat(res.getString("stemp")));

                                        sql="SELECT * FROM Humidity WHERE Device_idDevice = '"+deviceID+"'";
                                        res=SQLStatement.executeQuery(sql);
                                        res.next();
                                        deviceInfo.put("Humi",Float.parseFloat(res.getString("hygro")));

                                        deviceDatas.put(deviceInfo);

                                        break;

                                    case 1:

                                        sql="SELECT * FROM Doors WHERE Device_idDevice = '"+deviceID+"'";
                                        res=SQLStatement.executeQuery(sql);
                                        res.next();

                                        deviceInfo.put("Type","Door");
                                        deviceInfo.put("deviceID",deviceID);
                                        deviceInfo.put("State",res.getInt("isOpen"));
                                        deviceInfo.put("Password",Integer.parseInt(res.getString("Password")));
                                        deviceInfo.put("OpenTimes",Integer.parseInt(res.getString("opentimes")));
                                        deviceDatas.put(deviceInfo);




                                        break;

                                    case 2:

                                        sql="SELECT * FROM Windows WHERE Device_idDevice = '"+deviceID+"'";
                                        res=SQLStatement.executeQuery(sql);
                                        res.next();

                                        deviceInfo.put("Type","Window");
                                        deviceInfo.put("deviceID",deviceID);
                                        deviceInfo.put("State",res.getInt("isOpen"));
                                        deviceInfo.put("OpenTimes",Integer.parseInt(res.getString("opentimes")));
                                        deviceDatas.put(deviceInfo);

                                        break;

                                    case 3:

                                        sql="SELECT * FROM Lamps WHERE Device_idDevice = '"+deviceID+"'";
                                        res=SQLStatement.executeQuery(sql);
                                        res.next();

                                        deviceInfo.put("Type","Lamp");
                                        deviceInfo.put("deviceID",deviceID);
                                        deviceInfo.put("State",Integer.parseInt(res.getString("Gear")));
                                        deviceInfo.put("Color",Integer.parseInt(res.getString("Color")));
                                        deviceInfo.put("R",res.getInt("Red"));
                                        deviceInfo.put("G",res.getInt("Green"));
                                        deviceInfo.put("B",res.getInt("Blue"));
                                        deviceInfo.put("MaxLuminance",(int)(res.getDouble("maxLuminance")));
                                        deviceDatas.put(deviceInfo);

                                        break;

                                    case 4:

                                        sql="SELECT * FROM Alarm WHERE Device_idDevice = '"+deviceID+"'";
                                        res=SQLStatement.executeQuery(sql);
                                        res.next();

                                        deviceInfo.put("Type","" +
                                                "Alarm");
                                        deviceInfo.put("deviceID",deviceID);
                                        deviceInfo.put("AlarmTriggered",Integer.parseInt(res.getString("alarmTriggered")));
                                        deviceInfo.put("AlarmMode",Integer.parseInt(res.getString("alarmmode")));
                                        deviceDatas.put(deviceInfo);

                                        break;

                                    case 5:

                                        sql="SELECT * FROM AutoSunVisor WHERE Device_idDevice = '"+deviceID+"'";
                                        res=SQLStatement.executeQuery(sql);
                                        res.next();

                                        deviceInfo.put("Type","Cover");
                                        deviceInfo.put("deviceID",deviceID);
                                        deviceInfo.put("State",Integer.parseInt(res.getString("gear")));
                                        deviceInfo.put("Illumination",Float.parseFloat(res.getString("pbrightness")));
                                        deviceInfo.put("OpenIllumination",Float.parseFloat(res.getString("sbrightness")));
                                        deviceDatas.put(deviceInfo);

                                        break;

                                    case 6:

                                        sql="SELECT * FROM firealarm WHERE Device_idDevice = '"+deviceID+"'";
                                        res=SQLStatement.executeQuery(sql);
                                        res.next();

                                        deviceInfo.put("Type","FireAlarm");
                                        deviceInfo.put("deviceID",deviceID);
                                        deviceInfo.put("AlarmTriggered",res.getInt("istriggered"));

                                        deviceDatas.put(deviceInfo);

                                        break;


                                }

                            }

                            returnMsg.put("success", 1);
                            returnMsg.put("errmsg", "");
                            returnMsg.put("data",deviceDatas);

                            bw = new BufferedWriter(new OutputStreamWriter(socket.getOutputStream()));
                            bw.write(returnMsg.toString());

                            bw.flush();




                            break;

                        case "showuserinfo":

                            username=data.getString("username");


                            sql="SELECT * FROM Account WHERE aName = '"+username+"'";
                            res=SQLStatement.executeQuery(sql);
                            if(res.next()){
                                UID=res.getString("UID");
                                sql="SELECT * FROM User WHERE uUID = '"+UID+"'";
                                res=SQLStatement.executeQuery(sql);
                                res.next();

                                JSONObject userData=new JSONObject();
                                userData.put("gender",res.getString("gender"));
                                userData.put("phoneNum",res.getString("phoneNum"));
                                userData.put("dateOfBirth",res.getString("dateOfBirth"));
                                userData.put("familyID",res.getString("Family_familyID"));

                                returnMsg.put("success", 1);
                                returnMsg.put("errmsg", "");
                                returnMsg.put("data",userData);

                                bw = new BufferedWriter(new OutputStreamWriter(socket.getOutputStream()));
                                bw.write(returnMsg.toString());

                                bw.flush();

                            }
                            else{
                                returnMsg.put("success", 0);
                                returnMsg.put("errmsg", "Invalid username");

                                bw = new BufferedWriter(new OutputStreamWriter(socket.getOutputStream()));
                                bw.write(returnMsg.toString());

                            }

                            break;

                        case "updateinfo":

                            username=data.getString("username");

                            sql="SELECT * FROM Account WHERE aName = '"+username+"'";
                            res=SQLStatement.executeQuery(sql);
                            if(res.next()){
                                UID=res.getString("UID");

                                sql="UPDATE User SET gender = '"+data.getString("gender")+"' WHERE uUID = '"+UID+"'";
                                SQLStatement.execute(sql);

                                sql="UPDATE User SET phoneNum = '"+data.getString("phoneNum")+"' WHERE uUID = '"+UID+"'";
                                SQLStatement.execute(sql);

                                sql="UPDATE User SET Family_familyID = '"+data.getString("familyID")+"' WHERE uUID = '"+UID+"'";
                                SQLStatement.execute(sql);

                                sql="UPDATE User SET dateOfBirth = '"+data.getString("dateOfBirth")+"' WHERE uUID = '"+UID+"'";
                                SQLStatement.execute(sql);


                                returnMsg.put("success", 1);
                                returnMsg.put("errmsg", "");

                                bw = new BufferedWriter(new OutputStreamWriter(socket.getOutputStream()));
                                bw.write(returnMsg.toString());

                                bw.flush();

                            }
                            else{
                                returnMsg.put("success", 0);
                                returnMsg.put("errmsg", "Invalid username");

                                bw = new BufferedWriter(new OutputStreamWriter(socket.getOutputStream()));
                                bw.write(returnMsg.toString());

                                bw.flush();


                            }


                            break;


                        case "registerfamily":

                            sql="SELECT familyID FROM Family";
                            res= SQLStatement.executeQuery(sql);
                            int valid=1;
                            while(res.next()){
                                if(res.getString("familyID").equals(data.getString("familyID"))) {
                                    returnMsg.put("success", 0);
                                    returnMsg.put("errmsg", "Duplicate familyID");

                                    bw = new BufferedWriter(new OutputStreamWriter(socket.getOutputStream()));
                                    bw.write(returnMsg.toString());

                                    bw.flush();
                                    valid=0;
                                    break;

                                }
                            }
                            if(valid==1){
                                sql="INSERT INTO Family values('"+data.getString("familyID")+"', '"
                                        +data.getString("fName")+"', '"
                                        +data.getString("address")
                                        +"', 1)";
                                SQLStatement.execute(sql);

                                returnMsg.put("success", 1);
                                returnMsg.put("errmsg", "");

                                bw = new BufferedWriter(new OutputStreamWriter(socket.getOutputStream()));
                                bw.write(returnMsg.toString());

                                bw.flush();



                            }

                            break;


                        case "showsecurityinfo":


                            username=data.getString("username");

                            sql="SELECT * FROM Account WHERE aName = '"+username+"'";
                            res=SQLStatement.executeQuery(sql);
                            res.next();
                            UID=res.getString("UID");
                            sql="SELECT * FROM User WHERE uUID = '"+UID+"'";
                            res=SQLStatement.executeQuery(sql);
                            res.next();
                            familyID=res.getString("Family_familyID");
                            sql="SELECT * FROM Device WHERE Family_familyID = '"+familyID+"'";
                            resultset=SQLStatement.executeQuery(sql);
                            int countNum=0;
                            while(resultset.next()) countNum++;
                            String[] securityInfos=new String[countNum];

                            sql="SELECT * FROM Device WHERE Family_familyID = '"+familyID+"'";
                            resultset=SQLStatement.executeQuery(sql);
                            for(int i=0;i<countNum;i++){
                                resultset.next();
                                securityInfos[i]=resultset.getString("idDevice");

                            }
                            JSONObject alarmData=new JSONObject();

                            for(int i=0;i<countNum;i++){
                                String securityInfo=securityInfos[i];
                                sql="SELECT * FROM Alarm WHERE Device_idDevice = '"+securityInfo+"'";
                                res=SQLStatement.executeQuery(sql);
                                if(res.next()) alarmData.put("BreakInAlarm",Integer.parseInt(res.getString("AlarmTriggered")));

                                sql="SELECT * FROM firealarm WHERE Device_idDevice = '"+securityInfo+"'";
                                res=SQLStatement.executeQuery(sql);
                                if(res.next()) alarmData.put("FireAlarm",res.getInt("istriggered"));

                                sql="SELECT * FROM Temperature WHERE Device_idDevice = '"+securityInfo+"'";
                                res= SQLStatement.executeQuery(sql);
                                if(res.next()) alarmData.put("Temperature",Float.parseFloat(res.getString("tempNum")));

                                sql="SELECT * FROM Humidity WHERE Device_idDevice = '"+securityInfo+"'";
                                res= SQLStatement.executeQuery(sql);
                                if(res.next()) alarmData.put("Humidity",Float.parseFloat(res.getString("hygro")));

                            }


                            returnMsg.put("success", 1);
                            returnMsg.put("errmsg", "");
                            returnMsg.put("data",alarmData);

                            bw = new BufferedWriter(new OutputStreamWriter(socket.getOutputStream()));
                            bw.write(returnMsg.toString());

                            bw.flush();




                            break;


                        case "changedoorpsw":


                            changeDeviceID=data.getString("deviceID");

                            Long changePsw=data.getLong("changePsw");


                            sql="SELECT * FROM Doors WHERE Device_idDevice = '"+changeDeviceID+"'";
                            res=SQLStatement.executeQuery(sql);
                            if(res.next()){

                                sql="UPDATE Doors SET Password = '"+changePsw+
                                        "' WHERE Device_idDevice = '"+changeDeviceID+"'";
                                SQLStatement.execute(sql);

                                JSONObject option=new JSONObject();

                                option.put("optName","changePW");
                                option.put("newPassword",changePsw);



                                cacheDeviceID.add(changeDeviceID);
                                cache.add(option);

                                returnMsg.put("success", 1);
                                returnMsg.put("errmsg", "");

                                bw = new BufferedWriter(new OutputStreamWriter(socket.getOutputStream()));
                                bw.write(returnMsg.toString());

                                bw.flush();

                            }
                            else{
                                returnMsg.put("success", 0);
                                returnMsg.put("errmsg", "Invalid DeviceID");

                                bw = new BufferedWriter(new OutputStreamWriter(socket.getOutputStream()));
                                bw.write(returnMsg.toString());

                                bw.flush();
                            }

                           break;


                        case "changedoorstate":


                            changeDeviceID=data.getString("deviceID");

                            int changeDoorState=data.getInt("changeDoorState");

                            sql="SELECT * FROM Doors WHERE Device_idDevice = '"+changeDeviceID+"'";
                            res=SQLStatement.executeQuery(sql);
                            if(res.next()){

                                sql="UPDATE Doors SET isOpen = "+changeDoorState+
                                        " WHERE Device_idDevice = '"+changeDeviceID+"'";
                                SQLStatement.execute(sql);

                                JSONObject option=new JSONObject();

                                option.put("optName","changeDoorState");
                                option.put("newState",changeDoorState);



                                cacheDeviceID.add(changeDeviceID);
                                cache.add(option);

                                returnMsg.put("success", 1);
                                returnMsg.put("errmsg", "");

                                bw = new BufferedWriter(new OutputStreamWriter(socket.getOutputStream()));
                                bw.write(returnMsg.toString());

                                bw.flush();
                            }
                            else {
                                returnMsg.put("success", 0);
                                returnMsg.put("errmsg", "Invalid DeviceID");

                                bw = new BufferedWriter(new OutputStreamWriter(socket.getOutputStream()));
                                bw.write(returnMsg.toString());

                                bw.flush();

                            }

                            break;

                        case "changelight":


                            changeDeviceID=data.getString("deviceID");

                            int changeLight=data.getInt("changeLight");
                            int r=-1;
                            int g=-1;
                            int b=-1;
                            if(changeLight==0){
                                r=data.getInt("R");
                                g=data.getInt("G");
                                b=data.getInt("B");
                            }


                                sql="SELECT * FROM Lamps WHERE Device_idDevice = '"+changeDeviceID+"'";
                                res=SQLStatement.executeQuery(sql);
                                if(res.next()){

                                    sql="UPDATE Lamps SET color = '"+changeLight+
                                            "' WHERE Device_idDevice = '"+changeDeviceID+"'";
                                    SQLStatement.execute(sql);

                                    if(changeLight==0){
                                        sql="UPDATE Lamps SET Red = "+r+
                                                " WHERE Device_idDevice = '"+changeDeviceID+"'";
                                        SQLStatement.execute(sql);
                                        sql="UPDATE Lamps SET Green = "+g+
                                                " WHERE Device_idDevice = '"+changeDeviceID+"'";
                                        SQLStatement.execute(sql);
                                        sql="UPDATE Lamps SET Blue = "+b+
                                                " WHERE Device_idDevice = '"+changeDeviceID+"'";
                                        SQLStatement.execute(sql);
                                    }

                                    JSONObject option=new JSONObject();

                                    sql="SELECT * FROM Lamps WHERE Device_idDevice = '"+changeDeviceID+"'";
                                    res=SQLStatement.executeQuery(sql);
                                    res.next();

                                    option.put("optName","changeLight");
                                    option.put("level",Integer.parseInt(res.getString("gear")));
                                    option.put("color",changeLight);
                                    option.put("R",(changeLight==0?r:""));
                                    option.put("G",(changeLight==0?g:""));
                                    option.put("B",(changeLight==0?b:""));



                                    cacheDeviceID.add(changeDeviceID);
                                    cache.add(option);

                                    returnMsg.put("success", 1);
                                    returnMsg.put("errmsg", "");

                                    bw = new BufferedWriter(new OutputStreamWriter(socket.getOutputStream()));
                                    bw.write(returnMsg.toString());

                                    bw.flush();

                                }
                                else{
                                    returnMsg.put("success", 0);
                                    returnMsg.put("errmsg", "Invalid DeviceID");

                                    bw = new BufferedWriter(new OutputStreamWriter(socket.getOutputStream()));
                                    bw.write(returnMsg.toString());

                                    bw.flush();

                                }

                            break;

                        case "changelevel":


                            changeDeviceID=data.getString("deviceID");

                            int changeLevel=data.getInt("changeLevel");

                            sql="SELECT * FROM Lamps WHERE Device_idDevice = '"+changeDeviceID+"'";
                            res=SQLStatement.executeQuery(sql);
                            if(res.next()){

                                sql="UPDATE Lamps SET gear = '"+changeLevel+
                                        "' WHERE Device_idDevice = '"+changeDeviceID+"'";
                                SQLStatement.execute(sql);

                                if(changeLevel==0){
                                    sql="UPDATE Lamps SET isOn = 0" +
                                            " WHERE Device_idDevice = '"+changeDeviceID+"'";

                                    SQLStatement.execute(sql);
                                }
                                else{
                                    sql="UPDATE Lamps SET isOn = 1" +
                                            " WHERE Device_idDevice = '"+changeDeviceID+"'";

                                    SQLStatement.execute(sql);

                                }

                                JSONObject option=new JSONObject();

                                sql="SELECT * FROM Lamps WHERE Device_idDevice = '"+changeDeviceID+"'";
                                res=SQLStatement.executeQuery(sql);
                                res.next();
                                option.put("optName","changeLight");
                                option.put("level",changeLevel);
                                option.put("color",Integer.parseInt(res.getString("color")));
                                option.put("R",(res.getString("color")=="0"?
                                        res.getInt("Red"):""));
                                option.put("G",(res.getString("color")=="0"?
                                        res.getInt("Green"):""));
                                option.put("B",(res.getString("color")=="0"?
                                        res.getInt("Blue"):""));



                                cacheDeviceID.add(changeDeviceID);
                                cache.add(option);

                                returnMsg.put("success", 1);
                                returnMsg.put("errmsg", "");

                                bw = new BufferedWriter(new OutputStreamWriter(socket.getOutputStream()));
                                bw.write(returnMsg.toString());

                                bw.flush();

                            }
                            else{
                                returnMsg.put("success", 0);
                                returnMsg.put("errmsg", "Invalid DeviceID");

                                bw = new BufferedWriter(new OutputStreamWriter(socket.getOutputStream()));
                                bw.write(returnMsg.toString());

                                bw.flush();

                            }

                            break;

                        case "changefanstate":


                            changeDeviceID=data.getString("deviceID");

                            int changeFanState=data.getInt("changeFanState");

                            sql="SELECT * FROM Fan WHERE Device_idDevice = '"+changeDeviceID+"'";
                            res=SQLStatement.executeQuery(sql);
                            if(res.next()){

                                sql="UPDATE Fan SET Gear = "+changeFanState+
                                        " WHERE Device_idDevice = '"+changeDeviceID+"'";
                                SQLStatement.execute(sql);
                                if(changeFanState==0){
                                    sql="UPDATE Fan SET isOpen = 0" +
                                            " WHERE Device_idDevice = '"+changeDeviceID+"'";
                                    SQLStatement.execute(sql);

                                }
                                else{
                                    sql="UPDATE Fan SET isOpen = 1" +
                                            " WHERE Device_idDevice = '"+changeDeviceID+"'";
                                    SQLStatement.execute(sql);

                                }

                                JSONObject option=new JSONObject();

                                sql="SELECT * FROM Fan WHERE Device_idDevice = '"+changeDeviceID+"'";
                                res=SQLStatement.executeQuery(sql);
                                res.next();

                                option.put("optName","changeFanState");
                                option.put("newState",changeFanState);
                                option.put("newOpenTemp",Float.parseFloat(res.getString("stemp")));

                                cacheDeviceID.add(changeDeviceID);
                                cache.add(option);

                                returnMsg.put("success", 1);
                                returnMsg.put("errmsg", "");

                                bw = new BufferedWriter(new OutputStreamWriter(socket.getOutputStream()));
                                bw.write(returnMsg.toString());

                                bw.flush();

                            }
                            else{
                                returnMsg.put("success", 0);
                                returnMsg.put("errmsg", "Invalid DeviceID");

                                bw = new BufferedWriter(new OutputStreamWriter(socket.getOutputStream()));
                                bw.write(returnMsg.toString());

                                bw.flush();

                            }

                            break;

                        case "changefanopentemp":


                            changeDeviceID=data.getString("deviceID");

                            Float changeFanOpenTemp=data.getFloat("changeFanOpenTemp");

                            sql="SELECT * FROM Fan WHERE Device_idDevice = '"+changeDeviceID+"'";
                            res=SQLStatement.executeQuery(sql);
                            if(res.next()){

                                sql="UPDATE Fan SET stemp = '"+changeFanOpenTemp+
                                        "' WHERE Device_idDevice = '"+changeDeviceID+"'";
                                SQLStatement.execute(sql);


                                JSONObject option=new JSONObject();

                                sql="SELECT * FROM Fan WHERE Device_idDevice = '"+changeDeviceID+"'";
                                res=SQLStatement.executeQuery(sql);
                                res.next();


                                option.put("optName","changeFanState");
                                option.put("newState",res.getInt("Gear"));
                                option.put("newOpenTemp",changeFanOpenTemp);



                                cacheDeviceID.add(changeDeviceID);
                                cache.add(option);

                                returnMsg.put("success", 1);
                                returnMsg.put("errmsg", "");

                                bw = new BufferedWriter(new OutputStreamWriter(socket.getOutputStream()));
                                bw.write(returnMsg.toString());

                                bw.flush();
                            }
                            else{
                                returnMsg.put("success", 0);
                                returnMsg.put("errmsg", "Invalid DeviceID");

                                bw = new BufferedWriter(new OutputStreamWriter(socket.getOutputStream()));
                                bw.write(returnMsg.toString());

                                bw.flush();

                            }

                            break;


                        case "changecoverstate":

                            changeDeviceID=data.getString("deviceID");

                            int changeCoverState=data.getInt("changeCoverState");

                            sql="SELECT * FROM AutoSunVisor WHERE Device_idDevice = '"+changeDeviceID+"'";
                            res=SQLStatement.executeQuery(sql);
                            if(res.next()){

                                sql="UPDATE AutoSunVisor SET gear = '"+changeCoverState+
                                        "' WHERE Device_idDevice = '"+changeDeviceID+"'";
                                SQLStatement.execute(sql);
                                if(changeCoverState==0){
                                    sql="UPDATE AutoSunVisor SET isOpen = 0" +
                                            " WHERE Device_idDevice = '"+changeDeviceID+"'";
                                    SQLStatement.execute(sql);

                                }
                                else {
                                    sql="UPDATE AutoSunVisor SET isOpen = 1" +
                                            " WHERE Device_idDevice = '"+changeDeviceID+"'";
                                    SQLStatement.execute(sql);

                                }

                                JSONObject option=new JSONObject();

                                sql="SELECT * FROM AutoSunVisor WHERE Device_idDevice = '"+changeDeviceID+"'";
                                res=SQLStatement.executeQuery(sql);
                                res.next();

                                option.put("optName","changeCoverState");
                                option.put("newState",changeCoverState);
                                option.put("newOpenIllu",Float.parseFloat(res.getString("sbrightness")));



                                cacheDeviceID.add(changeDeviceID);
                                cache.add(option);

                                returnMsg.put("success", 1);
                                returnMsg.put("errmsg", "");

                                bw = new BufferedWriter(new OutputStreamWriter(socket.getOutputStream()));
                                bw.write(returnMsg.toString());

                                bw.flush();
                            }
                            else{
                                returnMsg.put("success", 0);
                                returnMsg.put("errmsg", "Invalid DeviceID");

                                bw = new BufferedWriter(new OutputStreamWriter(socket.getOutputStream()));
                                bw.write(returnMsg.toString());

                                bw.flush();

                            }

                            break;

                        case "changecoveropenillu":

                            changeDeviceID=data.getString("deviceID");

                            Float changeCoverOpenIllu=data.getFloat("changeCoverOpenIllu");

                            sql="SELECT * FROM AutoSunVisor WHERE Device_idDevice = '"+changeDeviceID+"'";
                            res=SQLStatement.executeQuery(sql);
                            if(res.next()){

                                sql="UPDATE AutoSunVisor SET sbrightness = '"+changeCoverOpenIllu+
                                        "' WHERE Device_idDevice = '"+changeDeviceID+"'";
                                SQLStatement.execute(sql);

                                JSONObject option=new JSONObject();

                                sql="SELECT * FROM AutoSunVisor WHERE Device_idDevice = '"+changeDeviceID+"'";
                                res=SQLStatement.executeQuery(sql);
                                res.next();

                                option.put("optName","changeCoverState");
                                option.put("newState",Integer.parseInt(res.getString("gear")));
                                option.put("newOpenIllu",changeCoverOpenIllu);



                                cacheDeviceID.add(changeDeviceID);
                                cache.add(option);

                                returnMsg.put("success", 1);
                                returnMsg.put("errmsg", "");

                                bw = new BufferedWriter(new OutputStreamWriter(socket.getOutputStream()));
                                bw.write(returnMsg.toString());

                                bw.flush();
                            }
                            else {
                                returnMsg.put("success", 0);
                                returnMsg.put("errmsg", "Invalid DeviceID");

                                bw = new BufferedWriter(new OutputStreamWriter(socket.getOutputStream()));
                                bw.write(returnMsg.toString());

                                bw.flush();

                            }

                            break;

                        case "changesecmode":


                            changeDeviceID=data.getString("deviceID");

                            int changeSecMode=data.getInt("changeSecMode");

                            sql="SELECT * FROM Alarm WHERE Device_idDevice = '"+changeDeviceID+"'";
                            res=SQLStatement.executeQuery(sql);
                            if(res.next()){

                                sql="UPDATE Alarm SET alarmmode = '"+changeSecMode+
                                        "' WHERE Device_idDevice = '"+changeDeviceID+"'";
                                SQLStatement.execute(sql);

                                JSONObject option=new JSONObject();

                                option.put("optName","changeSecMode");
                                option.put("secmode",changeSecMode);

                                cacheDeviceID.add(changeDeviceID);
                                cache.add(option);

                                returnMsg.put("success", 1);
                                returnMsg.put("errmsg", "");

                                bw = new BufferedWriter(new OutputStreamWriter(socket.getOutputStream()));
                                bw.write(returnMsg.toString());

                                bw.flush();
                            }
                            else {
                                returnMsg.put("success", 0);
                                returnMsg.put("errmsg", "Invalid DeviceID");

                                bw = new BufferedWriter(new OutputStreamWriter(socket.getOutputStream()));
                                bw.write(returnMsg.toString());

                                bw.flush();

                            }
                            break;
                    }


                } catch (IOException | SQLException | JSONException e) {
                    e.printStackTrace();
                    returnMsg.put("success", 0);
                    returnMsg.put("errmsg", e.toString());

                    bw = new BufferedWriter(new OutputStreamWriter(socket.getOutputStream()));
                    bw.write(returnMsg.toString());

                    bw.flush();

                }

                socket.close();
            }

        } catch (IOException e) {
            e.printStackTrace();
        }

    }



    public static void main(String[] args) throws Exception{

        Main main=new Main();
        Thread thread1=new Thread(() -> main.equipMsg());
        Thread thread2=new Thread(() -> main.usrMsg());

        thread1.start();
        thread2.start();


    }
}